In this article I will provide a short tutorial on how to use Cursor in SQL Server with Stored Procedure.
Note: For beginners in Cursor, please refer my article What is Cursor in SQL Server and how it works.
 
 

Database

I have made use of the following table Customers with the schema as follows.
Using Cursor in SQL Server Stored Procedure with example
 
I have already inserted few records in the table.
Using Cursor in SQL Server Stored Procedure with example
 
Note: You can download the database table SQL by clicking the download link below.
          Download SQL file
 
 

Using Cursor in SQL Server with Stored Procedure

Creating Stored Procedure

The below Stored Procedure makes use of a Cursor for printing the records from the Customers Table.
Note: For beginners in Stored Procedure, please refer my article What is Stored Procedure in SQL Server, how to create and use a Stored Procedure and its Advantages.
 
Inside the PrintCustomers_Cursor Stored Procedure, the Cursor is begin by declaring some variables for storing the fetched column data for the table.
Then, the Cursor is declared with a name and its type is set as READ_ONLY and along the FOR keyword the SELECT query is written.
Once the Cursor is setup, it is opened using the OPEN command and the first record is fetched into the variables.
Note: Whenever a record is fetched the @@FETCH_STATUS has value 0 and as soon as all the records returned by the SELECT query are fetched, its value changes to -1.
 
The Cursor is associated with a WHILE loop which executes until the @@FETCH_STATUS value becomes 0.
Inside the WHILE loop, the processing is done for the current record and then again the next record is fetched and the records are printed.
Finally, the Cursor is closed and deallocated using CLOSE and DEALLOCATE commands respectively.
Note: It is very important to deallocate a Cursor, otherwise it will stay in database and when you declare another Cursor with same name again, SQL Server will throw an error: A cursor with the name 'Cursor1' already exists.
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE PrintCustomers_Cursor
AS
BEGIN
      SET NOCOUNT ON;
 
      --DECLARE THE VARIABLES FOR HOLDING DATA.
      DECLARE @CustomerId INT
             ,@Name VARCHAR(100)
             ,@Country VARCHAR(100)
 
      --DECLARE AND SET COUNTER.
      DECLARE @Counter INT
      SET @Counter = 1
 
      --DECLARE THE CURSOR FOR A QUERY.
      DECLARE PrintCustomers CURSOR READ_ONLY
      FOR
      SELECT CustomerId, Name, Country
      FROM Customers
 
      --OPEN CURSOR.
      OPEN PrintCustomers
 
      --FETCH THE RECORD INTO THE VARIABLES.
      FETCH NEXT FROM PrintCustomers INTO
      @CustomerId, @Name, @Country
 
      --LOOP UNTIL RECORDS ARE AVAILABLE.
      WHILE @@FETCH_STATUS = 0
      BEGIN
             IF @Counter = 1
             BEGIN
                        PRINT 'CustomerID' + CHAR(9) + 'Name' + CHAR(9) + CHAR(9) + CHAR(9) + 'Country'
                        PRINT '------------------------------------'
             END
 
             --PRINT CURRENT RECORD.
             PRINT CAST(@CustomerId AS VARCHAR(10)) + CHAR(9) + CHAR(9) + CHAR(9) + @Name + CHAR(9) + @Country
    
             --INCREMENT COUNTER.
             SET @Counter = @Counter + 1
 
             --FETCH THE NEXT RECORD INTO THE VARIABLES.
             FETCH NEXT FROM PrintCustomers INTO
             @CustomerId, @Name, @Country
      END
 
      --CLOSE THE CURSOR.
      CLOSE PrintCustomers
      DEALLOCATE PrintCustomers
END
GO
 

Executing Stored Procedure

The Stored Procedure is executed using the following command.
-- Executing Stored Procedure
EXEC PrintCustomers_Cursor
 
 

Screenshot

Records printed by Stored Procedure

Using Cursor in SQL Server Stored Procedure with example
 
 

Downloads

Download Code